/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dados.SQLConnection;

import dados.Utente;
import dados.UtenteJaExisteException;
import dados.UtenteNaoExisteException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.GregorianCalendar;

/**
 *
 * @author rmb
 */
public class SQLUtente {
    
     private Connection connection;
     
     public SQLUtente(Connection con) {
         connection = con;
     }
    
     public void insereUtente(Utente u) throws UtenteJaExisteException {
        
        String utProc = "{call InserirUtente(?,?,?,?,?,?,?)}";        
        String utQuer = "select count(*) from utentes where bi = ?";
        
        try{
            PreparedStatement ps = connection.prepareStatement(utQuer);
            ps.setString(1, u.getBi());
            ResultSet rs = ps.executeQuery();
            
            int act = 0;
            
            while(rs.next())
                act = rs.getInt(1);
            
            if(act == 1)
                throw new UtenteJaExisteException();
            
            CallableStatement cs = connection.prepareCall(utProc);
            cs.setString(1, u.getNome());
            cs.setString(2, u.getBi());
            cs.setString(3, u.getMorada());
            cs.setString(4, u.getCod_postal());
            cs.setString(5, u.getLocalidade());
            cs.setDate(6, new Date(u.getDataNascimento().getTimeInMillis()));            
            cs.setString(7, u.getContacto());
                        
            cs.execute();
            connection.commit();
        }
        catch(SQLException e) {}          
    }
    
     
    public void eliminaUtente(String cod) throws UtenteNaoExisteException {
        
        String utProc = "{call Eliminar_Utente(?)}";        
        String utActQuer = "select ativo from utentes where cod_utente = ?";

        try{
            PreparedStatement ps = connection.prepareStatement(utActQuer);
            ps.setInt(1,Integer.parseInt(cod));
            ResultSet rs = ps.executeQuery();
            
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new UtenteNaoExisteException();
                       
            CallableStatement cs = connection.prepareCall(utProc);
            cs.setInt(1, Integer.parseInt(cod));
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}
        
    }
    
    public void editarUtenteContacto (String cod, String contacto) throws UtenteNaoExisteException {
        
        String utProc = "{call Editar_Utentes_Telefone(?,?)}";        
        String utActQuer = "select ativo from utentes where cod_utente = ?";

        try {            
            PreparedStatement ps = connection.prepareStatement(utActQuer);
            ps.setInt(1,Integer.parseInt(cod));
            ResultSet rs = ps.executeQuery();
            
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new UtenteNaoExisteException();
            
            CallableStatement cs = connection.prepareCall(utProc);
            cs.setInt(1,Integer.parseInt(cod));
            cs.setString(2,contacto);
            cs.execute();
            
            connection.commit();
            
        }
        catch(SQLException e) {}
        
    }
    
    
    public void editarUtenteDataNasc (String cod, GregorianCalendar dataNasc) throws UtenteNaoExisteException {
        
        String utProc = "{call Editar_Utentes_DataN(?,?)}";        
        String utActQuer = "select ativo from utentes where cod_utente = ?";

        try {            
            PreparedStatement ps = connection.prepareStatement(utActQuer);
            ps.setInt(1,Integer.parseInt(cod));
            ResultSet rs = ps.executeQuery();
            
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new UtenteNaoExisteException();
            
            CallableStatement cs = connection.prepareCall(utProc);
            cs.setInt(1,Integer.parseInt(cod));
            cs.setDate(2,new Date(dataNasc.getTimeInMillis()));
            cs.execute();
            
            connection.commit();
            
        }
        catch(SQLException e) {}
        
    }
    
    
    public void editarUtenteCodPostal (String cod, String cod_postal, String localidade) throws UtenteNaoExisteException {
        
        String utProc = "{call Editar_Utentes_Cod_Postal(?,?,?)}";        
        String utActQuer = "select ativo from utentes where cod_utente = ?";

        try {            
            PreparedStatement ps = connection.prepareStatement(utActQuer);
            ps.setInt(1,Integer.parseInt(cod));
            ResultSet rs = ps.executeQuery();
            
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new UtenteNaoExisteException();
            
            CallableStatement cs = connection.prepareCall(utProc);
            cs.setInt(1,Integer.parseInt(cod));
            cs.setString(2,cod_postal);
            cs.setString(3,localidade);
            cs.execute();
            
            connection.commit();
            
        }
        catch(SQLException e) {}
        
    }
    
    
    public void editarUtenteNome (String cod, String nome) throws UtenteNaoExisteException {
        
        String utProc = "{call Editar_Utente_Nome(?,?)}";        
        String utActQuer = "select ativo from utentes where cod_utente = ?";

        try {            
            PreparedStatement ps = connection.prepareStatement(utActQuer);
            ps.setInt(1,Integer.parseInt(cod));
            ResultSet rs = ps.executeQuery();
            
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new UtenteNaoExisteException();
            
            CallableStatement cs = connection.prepareCall(utProc);
            cs.setInt(1,Integer.parseInt(cod));
            cs.setString(2,nome);
            cs.execute();
            
            connection.commit();
            
        }
        catch(SQLException e) {}
        
    }
    
    
    public void editarUtenteMorada (String cod, String morada) throws UtenteNaoExisteException {
        
        String utProc = "{call Editar_Utente_Morada(?,?)}";        
        String utActQuer = "select ativo from utentes where cod_utente = ?";

        try {            
            PreparedStatement ps = connection.prepareStatement(utActQuer);
            ps.setInt(1,Integer.parseInt(cod));
            ResultSet rs = ps.executeQuery();
            
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new UtenteNaoExisteException();
            
            CallableStatement cs = connection.prepareCall(utProc);
            cs.setInt(1,Integer.parseInt(cod));
            cs.setString(2,morada);
            cs.execute();
            
            connection.commit();
            
        }
        catch(SQLException e) {}
        
    }
    
    
    
    public void editarUtenteBI (String cod, String bi) throws UtenteNaoExisteException {
        
        String utProc = "{call Editar_Utente_BI(?,?)}";        
        String utActQuer = "select ativo from utentes where cod_utente = ?";

        try {            
            PreparedStatement ps = connection.prepareStatement(utActQuer);
            ps.setInt(1,Integer.parseInt(cod));
            ResultSet rs = ps.executeQuery();
            
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new UtenteNaoExisteException();
            
            CallableStatement cs = connection.prepareCall(utProc);
            cs.setInt(1,Integer.parseInt(cod));
            cs.setString(2,bi);
            cs.execute();
            
            connection.commit();
            
        }
        catch(SQLException e) {}
        
    }
    
                      
    
    
    public ArrayList<Utente> getUtentesCod (String cod) {
        
        ArrayList<Utente> uts = new ArrayList<Utente>();
        String quer = "select count(*) from utentes where ativo = 1 and cod_utente like ?";
        String quer2 = "select * from utentes where ativo = 1 and cod_utente like ?";
        
        try {
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1,"%"+cod+"%");
            ResultSet rs = ps.executeQuery();
            int count = 0;
            
            while(rs.next()) {
                count = rs.getInt(1);
            }
            
            rs.close();
            ps.close();
            
            if(count < 20) {
                
                PreparedStatement ps2 = connection.prepareStatement(quer2);
                ps2.setString(1, "%"+cod+"%");
                ResultSet rs2 = ps2.executeQuery();                         
                GregorianCalendar g = new GregorianCalendar();
                
                while(rs2.next()) {                                         
                    g.setTime(rs2.getDate("data_nascimento"));                                         
                    
                    uts.add(new Utente(rs2.getString("cod_utente"), rs2.getString("nome"), rs2.getString("BI"), 
                                rs2.getString("morada"), rs2.getString("cod_postal"),  g ,
                                    getLocalidadeDescr(rs2.getString("cod_postal")), rs2.getString("telefone")));                                        
                }                            
            }            
        }
        catch(SQLException e) {
            
        }        
        return uts;
    }
    
    public ArrayList<Utente> getUtentesNome (String nome) {
        
        ArrayList<Utente> uts = new ArrayList<Utente>();
        String quer = "select count(*) from utentes where ativo = 1 and nome like ?";
        String quer2 = "select * from utentes where ativo = 1 and nome like ?";
        
        try {
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1,"%"+nome+"%");
            ResultSet rs = ps.executeQuery();
            int count = 0;
            
            while(rs.next()) {
                count = rs.getInt(1);
            }
            
            rs.close();
            ps.close();
            
            if(count < 20) {
                
                PreparedStatement ps2 = connection.prepareStatement(quer2);
                ps2.setString(1, "%"+nome+"%");
                ResultSet rs2 = ps2.executeQuery();                         
                GregorianCalendar g = new GregorianCalendar();
                
                while(rs2.next()) {                                         
                    g.setTime(rs2.getDate("data_nascimento"));                                         
                    
                    uts.add(new Utente(rs2.getString("cod_utente"), rs2.getString("nome"), rs2.getString("BI"), 
                                rs2.getString("morada"), rs2.getString("cod_postal"),  g ,
                                    getLocalidadeDescr(rs2.getString("cod_postal")), rs2.getString("telefone")));                                        
                }                            
            }            
        }
        catch(SQLException e) {
            
        }        
        return uts;
    }
    
    private String getLocalidadeDescr (String cod_postal) {
        
        String descr = null;
        String quer = "select descricao from cod_postal where cod_postal = ?";
        
        try {
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1, cod_postal);
            ResultSet rs = ps.executeQuery();
                                    
            while(rs.next())
                descr = rs.getString("descricao");
            
        }
        catch(SQLException e) {}
        
        return descr;        
    }
    
    public Utente getUtenteCod(String cod) {
        
        Utente ut = null;
        
        String quer = "select * from utentes where cod_utente = ?";
        
        try{
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            GregorianCalendar g = new GregorianCalendar();
            while(rs.next()) {                                         
                    g.setTime(rs.getDate("data_nascimento"));                                         
                    
                    ut = new Utente(rs.getString("cod_utente"), rs.getString("nome"), rs.getString("BI"), 
                                rs.getString("morada"), rs.getString("cod_postal"),  g ,
                                    getLocalidadeDescr(rs.getString("cod_postal")), rs.getString("telefone"));                                        
                }          
            
            
        }
        catch(SQLException e) {}
        
        
        return ut;
    }
    
}
